package com.boilerCloud.dao.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.boilerCloud.dao.BaseDao;
import com.boilerCloud.dto.HzlqswPage;
import com.boilerCloud.dto.sys.Dictionary;
import com.boilerCloud.dto.user.AppUser;
import com.boilerCloud.util.StringUtil;

@Repository
public class AppUserDao extends BaseDao {

	public List<AppUser> getAppUserList(AppUser object)throws Exception {
		String sql = "select * from app_user a where a.is_delete='n' and a.user_type=?";
		return this.queryForList(sql, AppUser.class, object.getUserType());
	}

	public AppUser getAppUserInfo(int id)throws Exception {
		String sql = "select * from app_user where id = ? limit 1";
		return this.queryForObject(sql, AppUser.class, id);
	}

	public void save(final AppUser model)throws Exception {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		final String sql = "insert into app_user(name,cell_phone,account,dealer_id,password,"
				+ "user_type,status,company_name,address,deposit_bank,bank_account,create_time,modify_time,is_delete) "
				+ "values(?,?,?,?,?,?,?,?,?,?,?,now(),now(),'n')";
		this.jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, model.getName());
				ps.setString(2, model.getCellPhone());
				ps.setString(3, model.getAccount());
				ps.setInt(4, model.getDealerId());
				ps.setString(5, model.getPassword());
				ps.setInt(6, model.getUserType());
				ps.setInt(7, model.getStatus());
				ps.setString(8, model.getCompanyName());
				ps.setString(9, model.getAddress());
				
				ps.setString(10, model.getDepositBank());
				ps.setString(11, model.getBankAccount());
				return ps;
			}
		}, keyHolder);
		model.setId(keyHolder.getKey().intValue());
		
		
	}

	public void edit(AppUser model) throws Exception{
		String sql = "update app_user set name=?,cell_phone=?,account=?,dealer_id=?,password=?,"
				+ "status=?,company_name=?,address=?,deposit_bank=?,bank_account=?,modify_time=now() where id=?";
		List<Object> param = new ArrayList<>();
		param.add(model.getName());
		param.add(model.getCellPhone());
		param.add(model.getAccount());
		param.add(model.getDealerId());
		param.add(model.getPassword());
		param.add(model.getStatus());
		param.add(model.getCompanyName());
		param.add(model.getAddress());
		param.add(model.getDepositBank());
		param.add(model.getBankAccount());
		param.add(model.getId());
		this.jdbcTemplate.update(sql,param.toArray());
	}

	public void qryList(AppUser model, HzlqswPage<AppUser> page)throws Exception {
		String countsql = "select count(*) from app_user a "
				+ " left join app_user b on b.id = a.dealer_id"
				+ " where a.is_delete='n' ";
			
		String listSql = "select a.*,b.name dealerName "
					+ " from app_user a "
					+ " left join app_user b on b.id = a.dealer_id"
					+ " where a.is_delete='n' ";
		List<Object> params= new ArrayList<Object>();
		if(!StringUtil.isEmpty(model.getName())){
			countsql += " and a.name like ?";
			listSql += " and a.name like ?";
			params.add("%" + model.getName() +"%");
		}
		if(!StringUtil.isEmpty(model.getDealerName())){
			countsql += " and b.name like ?";
			listSql += " and b.name like ?";
			params.add("%" + model.getDealerName() +"%");
		}
		if(!StringUtil.isEmpty(model.getCellPhone())){
			countsql += " and a.cell_phone like ?";
			listSql += " and a.cell_phone like ?";
			params.add("%" + model.getCellPhone() +"%");
		}
		if(model.getUserType() != 0 && model.getUserType()!= -1){
			countsql += " and a.user_type= ?";
			listSql += " and  a.user_type= ?";
			params.add(model.getUserType() );
		}
			
		listSql += " order by id desc ";
		this.queryListByPage(countsql, params, listSql, params, page, AppUser.class);
		
	}

	public void del(int id) throws Exception {
		String sql = "update app_user set is_delete='y' where id = ?";
		this.jdbcTemplate.update(sql,id);
	}
	
	public List<AppUser> getReceiverList()throws Exception {
		String sql = "select * from app_user a where a.is_delete='n' and a.user_type= 1 "
				+ " union all select * from app_user a where a.is_delete='n' and a.user_type= 3";
		return this.queryForList(sql, AppUser.class);
	}

}
